package chase.myrewards;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.*;
import javax.servlet.http.*;

import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.appengine.api.utils.SystemProperty;

import chase.myrewards.beans.Account;
import chase.myrewards.beans.AccountList;
import chase.myrewards.beans.Customer;

/**
 * Servlet that takes customer id and account number as input and 
 * gets all the information of the account and puts it in a bean.
 * 
 * @author Meena Abdou
 * @date April 23, 2013
 */

public class AccountInfoServlet extends HttpServlet implements Serializable {

	/**
	 * This number must be unique for every class. And it can never change.
	 * You have to generate it automatically by clicking on the class name and
	 * then hitting Ctrl+1.  Then click on Add generated serial version ID. Then
	 * copy these comments on top of the generated id.
	 * 
	 * @serial serialVersionUID
	 */
	private static final long serialVersionUID = 6078292608538927591L;
	private static final Logger log = Logger.getLogger(AccountListServlet.class.getName());
	private Customer cust;
	private Account account;
	
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		HttpSession session = request.getSession();
		String address = "/WEB-INF/jsp/accountinfo.jsp";
		String norewards_address = "/WEB-INF/jsp/notavailable.jsp";
		boolean showrewards;
		cust = null;
		account = null;
		
		log.info("Session Id is " + request.getSession().getId());
		
		//Added by Archi
		//Making connection with the database
		//Getting reward balances and other information from database 

		if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Production) {
			Connection c = null;
			try {
				DriverManager.registerDriver(new AppEngineDriver());
				c = DriverManager.getConnection("jdbc:google:rdbms://myrewards2013:myrewardsdatabase/myrewards");				
				
				// Corrected by Archi on May 9th

				int i = Integer.parseInt((request.getParameter("accountindex")));
				AccountList acclist = (AccountList) session.getAttribute("accountlist"); 
				cust = (Customer) session.getAttribute("customer");
				account = acclist.getAccountList().get(i); // getting account number
				String id = cust.getCustomerID();
				String num = account.getAccountNum();
				
				java.sql.Statement stmt1 =  c.createStatement();
				java.sql.ResultSet rs1 = stmt1.executeQuery(Queries.ACCOUNT_INFO(num, id));
				rs1.next();

				/*
				log.info("Column Names");
				ResultSetMetaData rsmd = rs1.getMetaData();
				int columnCount = rsmd.getColumnCount();

				// The column count starts from 1
				for (int index = 1; index < columnCount + 1; index++ ) {
				  String name = rsmd.getColumnName(index);
				  log.info(name);
				}
				*/
				
				// Loading the resulting data from the query into account
				account.setCycleNum(rs1.getInt("account.cycle_num"));
				account.setPreviousBalance(rs1.getInt("sum(balances.previous)"));
				account.setRecentlyAccrued(rs1.getInt("sum(balances.recently_accrued)"));
				account.setUsedPoints(rs1.getInt("sum(balances.used)"));
				account.setAccountNum(rs1.getString("account.account_num"));

				// Computing current and future balances
				// Still need to do here: get the app date and check if current needs to be previous 
				int currbalance = (account.getPreviousBalance()- account.getUsedPoints())+ account.getRecentlyAccrued();
				int futurebalance = (account.getPreviousBalance()- account.getUsedPoints())+ account.getRecentlyAccrued();
				account.setFutureBalance(futurebalance);
				account.setCurrentBalance(currbalance);

				// Getting redemption channels for each account by program id
				java.sql.Statement stmt2 =  c.createStatement();
				java.sql.ResultSet rs2 = stmt2.executeQuery(Queries.REDEMPTION_CHANNELS(num));
				rs2.next();
								
				// Loading program id and redemption channels into the bean
				if(account.getRedeemNum().isEmpty() &&
				   account.getRedeemUrl().isEmpty() &&
				   account.getRedeemStore().isEmpty() &&
				   account.getRedeemCard().isEmpty()){
					do {
						account.insertChannels(rs2.getString("program.program_id"),rs2.getString("program.phone"), rs2.getString("program.web"), rs2.getString("program.store"),rs2.getString("program.card"));
					} while(rs2.next());
				}

				//Getting rewards available status			 	 
				java.sql.Statement stmt3 =  c.createStatement();
				java.sql.ResultSet rs3 = stmt3.executeQuery(Queries.REWARDS_AVAILABLE(num, id));
				rs3.next();
				showrewards = rs3.getBoolean("rewards_available");

				session.setAttribute("accountinfo", account);

				// To display that no rewards are available as rewards available is not true
				if(!showrewards){
					RequestDispatcher dispatcher = request.getRequestDispatcher(norewards_address);
					dispatcher.forward(request, response);
				}
				
				else {
					RequestDispatcher dispatcher = request.getRequestDispatcher(address);
					dispatcher.forward(request, response);
				}
			}
			catch (SQLException e) {
				log.log(Level.SEVERE, "SQLException", e);
				response.getWriter().write("SQLException " + e);
			}
			finally {
				if (c != null){
					try {
						c.close();
					}
					catch (SQLException ignore) {}
				}
			} // End of try-catch block
		}

		else {
			// Since we don't have database code yet, I have just created a mock account
			int i = Integer.parseInt((request.getParameter("accountindex")));
			AccountList al = (AccountList) session.getAttribute("accountlist"); //new Account("0", "sapphire");
			account = al.getAccountList().get(i);
			account.setCurrentBalance(1);
			account.setCycleNum(2);
			account.setFutureBalance(3);
			account.setPreviousBalance(4);
			account.setRecentlyAccrued(5);
			account.setUsedPoints(6);
			account.insertChannels("p1", "1-800-237-5239", "a.out", "this", "no");
			System.out.println(account.getSize());
			session.setAttribute("accountinfo", account);
			RequestDispatcher dispatcher = request.getRequestDispatcher(address);
			dispatcher.forward(request, response);
		}
		return;
	}
}
